package com.servlet;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 * Servlet implementation class SelectFlightServlet
 */
public class SelectFlightServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
private final static String DATASOURCE_NAME = "jdbc/FlyGreenwichAirline";
	
	private DataSource datasource;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SelectFlightServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		Integer customerid = 0;
		Timestamp timestamp = null;
		
		String flightDate = request.getParameter("flightdateselect");
		String flightid = request.getParameter("flightidselect");
		Integer id = Integer.parseInt(flightid);
		String name = request.getParameter("name");
		String address1 = request.getParameter("address1");
		String address2 = request.getParameter("address2");
		String postcode = request.getParameter("postcode");
		Integer postcodeInt = 0;
		Integer seat_no = 0;
		List<Integer> seatlist = new ArrayList<Integer>();
		
		if(postcode != null || postcode != ""){
			postcodeInt = Integer.parseInt(postcode);
		}		
		String country = request.getParameter("country");
		
		java.sql.Connection con=null;
				
		SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
		try {
			Date parsedDate = dateFormat.parse(flightDate);
			timestamp = new Timestamp(parsedDate.getTime());
		} catch (ParseException e1) {
			e1.printStackTrace();
		}		

    	String query = "INSERT INTO customers (name,address1,address2,country,postcode,flight_no) values (?,?,?,?,?,?);";
    	String query2 = "SELECT id FROM customers WHERE name = '"+name+"';";
    	String query3 = "SELECT seat_no FROM transactions WHERE flight_id = "+id+" AND select_time = '"+timestamp+"';";
		
    	try{    		
    		Context initialContext = new InitialContext();
    		Context context = (Context)initialContext.lookup("java:comp/env");
    		datasource = (DataSource)context.lookup(DATASOURCE_NAME);

    		con = datasource.getConnection();
    	
    		java.sql.PreparedStatement pst = con.prepareStatement(query);
    		
    		pst.setString(1, name);
    		pst.setString(2, address1);
    		pst.setString(3, address2);
    		pst.setString(4, country);
    		pst.setInt(5, postcodeInt);
    		pst.setInt(6, id);
    		
    		pst.executeUpdate();
    		
    		java.sql.Statement stmt2 = con.createStatement();
            ResultSet rs = stmt2.executeQuery (query2);
            
            while(rs.next()){
            	customerid = rs.getInt(1);
            }
            
            java.sql.Statement stmt3 = con.createStatement();
            ResultSet rs2 = stmt3.executeQuery (query3);
            
            while(rs2.next()){
            	seat_no = rs2.getInt(1);
            	seatlist.add(seat_no);
            }
            
            rs.close();
            stmt2.close();
            
            rs2.close();
            stmt3.close();
    		
    	}catch(SQLException e){
    		e.printStackTrace();
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally{
    		try{
    			if (con != null) con.close();
    		}catch(SQLException ignored){
    		ignored.printStackTrace();
    		}
    	}
    	
    	System.out.println("name: "+name);
    	System.out.println("customerid: "+customerid);
    	System.out.println("flightid: "+flightid);
    	System.out.println("seatlist: "+seatlist.size());
    	System.out.println("date: "+flightDate);
    	
    	request.setAttribute("name", name);
    	request.setAttribute("customerid", customerid);
    	request.setAttribute("flightid", id);
    	request.setAttribute("seatlist", seatlist);
    	request.setAttribute("date", flightDate);
    	
    	String nextJSP = "/chooseFlight.jsp";
    	RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(nextJSP);
    	dispatcher.forward(request,response);
	}

}
